﻿using System;
using System.Collections.Generic;
using System.Data;

using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
//using Oracle.DataAccess.Types;

using LMD.Constant;
using LMD.Lib.DataClass;
using LMD.Lib.Oracle;

namespace DBQuery
{
    /// <summary>
    /// Summary description for ICPUserTable
    /// </summary>
    public static class ICPUserTable
    {
        public static void UpdateCustomData(UserData udToUpdate)
        {
            Cached.ICPUserTable ccUser = new Cached.ICPUserTable();
            UserData cpdExisting = new UserData();
            cpdExisting = ccUser.GetUserData(udToUpdate.ID, true);

            Dictionary<String, String> dicNew = udToUpdate.GetCustomDataDictionary();
            Dictionary<String, String> dicExisting = cpdExisting.GetCustomDataDictionary();

            DBLib dbl = new DBLib();
            dbl.BeginTransaction();
            try
            {
                foreach (KeyValuePair<String, String> item in dicNew)
                {

                    int iUserProfileDataType = Convert.ToInt32(item.Key);

                    if (dicExisting.ContainsKey(item.Key))
                    {
                        // UPDATE
                        if (String.IsNullOrEmpty(item.Value))
                        {
                            DeleteUserProfileData(dbl, udToUpdate.ID, iUserProfileDataType);
                        }
                        else
                        {
                            UpdateUserProfileData(dbl, udToUpdate.ID, iUserProfileDataType, item.Value);
                        }

                        dicExisting.Remove(item.Key);
                    }
                    else
                    {
                        // INSERT
                        InsertUserProfileData(dbl, udToUpdate.ID, iUserProfileDataType, item.Value);

                        String x = "1";
                        int y = int.Parse(x);
                    }
                }

                foreach (KeyValuePair<String, String> item in dicExisting)
                {
                    // DELETE YG SUDAH TIDAK ADA LAGI
                    int iInfoType = Convert.ToInt32(item.Key);
                    DeleteUserProfileData(dbl, udToUpdate.ID, iInfoType);
                }

            }
            catch (Exception ex)
            {
                dbl.EndTransaction(false);
                throw ex;
            }
            dbl.EndTransaction(true);
        }

        public static int InsertUserProfileData(String sxUserId, int iUserDataTypeId, String sxValue)
        {
            DBLib dbl = new DBLib();
            int iReturn = InsertUserProfileData(dbl, sxUserId, iUserDataTypeId, sxValue);
            
            return iReturn;
        }
        public static int InsertUserProfileData(DBLib dbl, String sxUserId, int iUserDataTypeId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "out_new_id";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Direction = ParameterDirection.Output;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_ICP_USER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxUserId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_USER_PROFILE_DATA_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iUserDataTypeId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_CREATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_INSERT_T_USER_PROFILE_DATA";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
            return Convert.ToInt32(lsParams[0].Value);
        }

        public static void UpdateUserProfileData(String sxUserId, int iUserDataTypeId, String sxValue)
        {
            DBLib dbl = new DBLib();
            UpdateUserProfileData(dbl, sxUserId, iUserDataTypeId, sxValue);
            
        }
        public static void UpdateUserProfileData(DBLib dbl, String sxUserId, int iUserDataTypeId, String sxValue)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_VALUE";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxValue;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_ICP_USER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxUserId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_USER_PROFILE_DATA_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iUserDataTypeId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_UPDATE_BY";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = ICPUser.ID;

            String sxQuery = "PR_UPDATE_T_USER_PROFILE_DATA";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }

        public static void DeleteUserProfileData(String sxUserId, int iUserDataTypeId)
        {
            DBLib dbl = new DBLib();
            DeleteUserProfileData(dbl, sxUserId, iUserDataTypeId);
            
        }
        public static void DeleteUserProfileData(DBLib dbl, String sxUserId, int iUserDataTypeId)
        {
            List<OracleParameter> lsParams = new List<OracleParameter>();

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_ICP_USER_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.VarChar;
            lsParams[lsParams.Count - 1].Value = sxUserId;

            lsParams.Add(new OracleParameter());
            lsParams[lsParams.Count - 1].ParameterName = "in_P_USER_PROFILE_DATA_TYPE_ID";
            lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
            lsParams[lsParams.Count - 1].Value = iUserDataTypeId;

            String sxQuery = "PR_DELETE_T_USER_PROFILE_DATA";

            dbl.ExecuteProcedure(sxQuery, lsParams.ToArray());
        }
    }
}